/*

exec sp_load_all_base_products

select processtypeid,base_process_type from process_types_table
group by processtypeid,base_process_type

select * from product where p_parent_product_id = -1 


*/
ALTER    PROC sp_load_all_base_products
as
BEGIN
	DECLARE @results Table(ProcessStep_ID int, IST_Manufacturing_Part_Number varchar (50), IST_Marketing_Part_Number varchar (50), Product_ID int, P_Type varchar (50), P_Comments varchar (200) )
	DECLARE @proc_id varchar (20)
	DECLARE @mft_part_no varchar (50)
	DECLARE @mkt_part_no varchar (50)
	DECLARE @prod_id int
	DECLARE @p_type varchar (50)
	DECLARE @p_description varchar (200)

	DECLARE ncursor CURSOR FOR
	SELECT ProcessStep_ID, Part_Number, P_Marketing_part_Number, Product_ID, P_Type, P_Comments FROM Product WHERE P_Type IS NULL and P_Parent_Product_Id IS NULL order by Part_Number

	open ncursor
	fetch next from ncursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type, @p_description 
	while @@fetch_status = 0
		BEGIN
			insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description)
			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null
			fetch next from ncursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type, @p_description 
		END
	close ncursor
	deallocate ncursor


	insert into @results values(null,'---- BOARD -----','---- BOARD -----',null,null,null)



	DECLARE bcursor CURSOR for
		SELECT ProcessStep_ID, Part_Number, P_Marketing_part_Number, Product_ID, P_Type, P_Comments FROM Product WHERE P_Type = 'BOARD' and Is_Approved=0  and P_Parent_Product_Id = -1 order by Part_Number
	

	open bcursor
		fetch next from bcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description 
		while @@fetch_status = 0
		BEGIN
			insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description)
			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null
			fetch next from bcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description 
		END
	close bcursor
	deallocate bcursor

	insert into @results values(null,'---- KIT -----','---- KIT -----',null,null,null)

	DECLARE kcursor CURSOR for
	SELECT ProcessStep_ID, Part_Number, P_Marketing_part_Number, Product_ID, P_Type, P_Comments FROM Product WHERE P_Type = 'KIT' and Is_Approved=0  and P_Parent_Product_Id = -1 order by Part_Number

	open kcursor
		fetch next from kcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description 
		while @@fetch_status = 0
		BEGIN
			insert into @results values(@proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description )
			set @proc_id = null
			set @mft_part_no = null
			set @mkt_part_no = null
			set @prod_id = null
			set @p_type = null
			set @p_description = null
			fetch next from kcursor into @proc_id,@mft_part_no,@mkt_part_no,@prod_id,@p_type,@p_description 

		END
	close kcursor
	deallocate kcursor
END
SELECT * FROM @results   
